package DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Vector;

public class GetItemsByCategory {
	
	private String category;
	private String suppId;
	
	
	public GetItemsByCategory(String cat,String supp)
	{
		this.category=cat;
		this.suppId=supp;
	}
	
	
	
	
	public Vector<Vector> getAllItems ()
	{
		Vector<Vector> allItems =new Vector<Vector>();		
        SQLConnection sc=new SQLConnection();
		
		
        if (category.equals("כל המוצרים"))
        {
        	try {			
        		PreparedStatement ps = sc.getConnection().prepareStatement("SELECT i.iid ,i.Iname FROM item i WHERE i.iid NOT in (SELECT si.iid FROM  supplier_item si WHERE si.suppid=?) ;");	

        		ps.setString(1,suppId);
        		
        		ResultSet rs=ps.executeQuery();	

        		ResultSetMetaData rsmd = rs.getMetaData();
        		
        		 while(rs.next())
        	        {   

        			 Vector<String>  row = new Vector<String>();
        	         for(int col = 1; col <= rsmd.getColumnCount(); col++) 
        	         	{                      	                	               
        	                 row.add(rs.getString(col));
        	         	}//for
        	         allItems.add(row);
        	        }//while
        		 ps.close();
        		 rs.close();
        		
        		} catch (SQLException e) {
        			// TODO Auto-generated catch block
        			e.printStackTrace();
        		}
        		return allItems;
        	
        }
        
        else
        {
        
		try {			
		PreparedStatement ps = sc.getConnection().prepareStatement("SELECT i.iid ,i.Iname FROM item i WHERE i.iid NOT in (SELECT si.iid FROM  supplier_item si WHERE si.suppid=?) AND i.ICategory=?;");	

		ps.setString(1,suppId);
		ps.setString(2, category);
		

		ResultSet rs=ps.executeQuery();	

		ResultSetMetaData rsmd = rs.getMetaData();
		
		 while(rs.next())
	        {   

			 Vector<String>  row = new Vector<String>();
	         for(int col = 1; col <= rsmd.getColumnCount(); col++) 
	         	{                      	                	               
	                 row.add(rs.getString(col));
	         	}//for
	         allItems.add(row);
	        }//while
		 ps.close();
		 rs.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return allItems;
	}

	}
	
	
	
	
	
	
	
	
	
	
	
	public Vector<Vector> getSuppItems()
	{
		Vector<Vector> supItems=new Vector<Vector>();
		
     SQLConnection sc=new SQLConnection();
		
 	
     if (category.equals("כל המוצרים"))
     {
		try {
			
		PreparedStatement ps = sc.getConnection().prepareStatement("SELECT i.iid,i.Iname,si.suppItemPrice FROM  supplier_item si,item i WHERE si.suppid=? AND si.iid=i.iid ;");	
        ps.setString(1,suppId);
       
		
		ResultSet rs=ps.executeQuery();	

		ResultSetMetaData rsmd = rs.getMetaData();
		
		 while(rs.next())
	        {   

			 Vector<String>  row = new Vector<String>();
	         for(int col = 1; col <= rsmd.getColumnCount(); col++) 
	         	{                      	                	               
	                 row.add(rs.getString(col));	               
	         	}//for
	         supItems.add(row);
	        }//while
		 ps.close();
		 rs.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return supItems;
	}
	
	
	else
	{
		try {
			
			PreparedStatement ps = sc.getConnection().prepareStatement("SELECT i.iid,i.Iname,si.suppItemPrice FROM  supplier_item si,item i WHERE si.suppid=? AND si.iid=i.iid AND i.ICategory=? ;");	
	        ps.setString(1,suppId);
	        ps.setString(2,category);
			
			ResultSet rs=ps.executeQuery();	

			ResultSetMetaData rsmd = rs.getMetaData();
			
			 while(rs.next())
		        {   

				 Vector<String>  row = new Vector<String>();
		         for(int col = 1; col <= rsmd.getColumnCount(); col++) 
		         	{                      	                	               
		                 row.add(rs.getString(col));	               
		         	}//for
		         supItems.add(row);
		        }//while
			    ps.close();
				rs.close();
				sc.getConnection().close();
			
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
			return supItems;
	}
}
}
